﻿using System;
using System.Data;
using System.Data.SqlClient;
using AccessControl.Data.Models;
using AccessControl.Contracts.Models;
using System.Collections.Generic;
using System.Net;

namespace AccessControl.DataManagers.Tables.Managers
{
    /// <summary>
    /// Manages the sensors in the DB
    /// </summary>
    class SensorsManager : TableManager<ISensor>
    {
        #region Fields

        private SqlDataAdapter _adapter;
        private static SensorsManager s_Instance = null;

		private static readonly string COL_1_ID = "ID";
		private static readonly string COL_2_TYPE = "TYPE";
		private static readonly string COL_3_IP = "IP";
		private static readonly string COL_4_PORT_NUMBER = "PORT_NUMBER";
		private static readonly string COL_5_RELATED_ENTRY_ID = "RELATED_ENTRY_ID";

        #endregion

        #region Private Constructor

        /// <summary>
        /// Prevents a default instance of the <see cref="SensorsManager"/> class from being created.
        /// </summary>
        private SensorsManager()
        {
        }

        #endregion

        #region Public Properties

        /// <summary>
        /// Gets the instance.
        /// </summary>
        public static SensorsManager Instance
        {
            get
            {
                lock (typeof(SensorsManager))
                {
                    if (s_Instance == null)
                        s_Instance = new SensorsManager();

                    return s_Instance;
                }
            }
        }

        #endregion
        
        #region TableManager Overrides

        /// <summary>
        /// Creates using the specified connection.
        /// </summary>
        /// <param name="connection">The connection.</param>
        public override void Create(SqlConnection connection)
        {
            SqlCommand command = connection.CreateCommand();
            command.CommandText = string.Format("CREATE TABLE {0} ({1} INT, {2} INT, {3} TEXT, {4} INT, {5} INT)", 
												TablesNames.SENSORS, COL_1_ID, COL_2_TYPE, COL_3_IP, COL_4_PORT_NUMBER, COL_5_RELATED_ENTRY_ID);
            command.ExecuteNonQuery();
        }

        /// <summary>
        /// Initializes this instance.
        /// </summary>
        public override void Initialize(SqlConnection connection)
        {
			/* Create the adapter */
			_adapter = new SqlDataAdapter();
			/* Create the adapter commands */
			_adapter.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TablesNames.SENSORS), connection);

			_adapter.InsertCommand = new SqlCommand((string.Format("INSERT INTO {0} ({1}, {2}, {3}, {4}, {5}) VALUES (@{1}, @{2}, @{3}, @{4}, @{5})",
													 TablesNames.SENSORS, COL_1_ID, COL_2_TYPE, COL_3_IP, COL_4_PORT_NUMBER, COL_5_RELATED_ENTRY_ID)), connection);

			_adapter.UpdateCommand = new SqlCommand((string.Format("UPDATE {0} SET {2} = @{2}, {3} = @{3}, {4} = @{4}, {5} = @{5} WHERE {1} = @{1}",
													 TablesNames.SENSORS, COL_1_ID, COL_2_TYPE, COL_3_IP, COL_4_PORT_NUMBER, COL_5_RELATED_ENTRY_ID)), connection);

			_adapter.DeleteCommand = new SqlCommand(string.Format("DELETE FROM {0} WHERE {1} = @{1}",
													TablesNames.SENSORS, COL_1_ID), connection);
        }

        /// <summary>
        /// Gets the list.
        /// </summary>
        /// <returns></returns>
        public override bool FillList(IList<ISensor> items)
        {
			Sensor sensor;

			using (SqlDataReader reader = _adapter.SelectCommand.ExecuteReader())
			{
				while (reader.Read())
				{
					sensor = new Sensor()
					{
						Id = reader.GetInt32(0),
						Ip = IPAddress.Parse(reader.GetString(2)),
						PortNumber = reader.GetInt32(3),
						RelatedEntryId = reader.GetInt32(4),
						Type = (SensorType)reader.GetInt32(1)
					};

					items.Add(sensor);
				}
			}

			return true;
        }

        /// <summary>
        /// Adds the specified item.
        /// </summary>
        /// <param name="item">The item.</param>
		public override bool Add(ISensor item)
        {
			_adapter.InsertCommand.Parameters.Clear();

			/* Set the item id */
			item.Id = GetHighestId(_adapter.SelectCommand.Connection, TablesNames.SENSORS, COL_1_ID) + 1;

			_adapter.InsertCommand.Parameters.Add(new SqlParameter(COL_1_ID, item.Id));
			_adapter.InsertCommand.Parameters.Add(new SqlParameter(COL_2_TYPE, (int)item.Type));
			_adapter.InsertCommand.Parameters.Add(new SqlParameter(COL_3_IP, item.Ip.ToString()));
			_adapter.InsertCommand.Parameters.Add(new SqlParameter(COL_4_PORT_NUMBER, item.PortNumber));
			_adapter.InsertCommand.Parameters.Add(new SqlParameter(COL_5_RELATED_ENTRY_ID, item.RelatedEntryId));

			return (_adapter.InsertCommand.ExecuteNonQuery() > 0);
        }

        /// <summary>
        /// Updates the specified item.
        /// </summary>
        /// <param name="item">The item.</param>
		public override bool Update(ISensor item)
        {
			_adapter.UpdateCommand.Parameters.Clear();

			_adapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_1_ID, item.Id));
			_adapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_2_TYPE, (int)item.Type));
			_adapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_3_IP, item.Ip.ToString()));
			_adapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_4_PORT_NUMBER, item.PortNumber));
			_adapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_5_RELATED_ENTRY_ID, item.RelatedEntryId));

			return (_adapter.UpdateCommand.ExecuteNonQuery() > 0);
        }

        /// <summary>
        /// Deletes the specified item.
        /// </summary>
        /// <param name="item">The item.</param>
		public override bool Delete(ISensor item)
        {
			_adapter.DeleteCommand.Parameters.Clear();

			_adapter.DeleteCommand.Parameters.Add(new SqlParameter(COL_1_ID, item.Id));

			return (_adapter.DeleteCommand.ExecuteNonQuery() > 0);
        }

        #endregion
    }
}
